package ch.zbw.pra.sysventory.database.jdbcTemplate;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.mysql.jdbc.Statement;

import ch.zbw.pra.sysventory.database.mapper.SoftwareMapper;
import ch.zbw.pra.sysventory.model.Software;

/**
 * Greift auf die Datenbank zu (DB-Tabelle Software) und delegiert das Mapping der Tabellenspalten an den RowMapper
 * Klasse kann auf die Datenbank schreiben,lesen und löschen
 * 
 * @author Roberto Lucas
 */

public class SoftwareJDBCTemplate implements Serializable {
	
	private static final long serialVersionUID = 706184203241005707L;
	private static Logger LOG = LoggerFactory.getLogger(SoftwareJDBCTemplate.class);
	private JdbcTemplate jdbcTemplateObject;
	
	private static final String INSERT_SQL = "INSERT INTO Software (softName, softVersion, softVendor,soft_CompID_fk) VALUES(?,?,?,?)";

	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplateObject = new JdbcTemplate(dataSource);
	}
	
	public Integer insertSoftware(final Software software, final Integer computerId) {
		KeyHolder keyHolder = new GeneratedKeyHolder();	
		try{
		jdbcTemplateObject.update(new PreparedStatementCreator() { 
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { 
				PreparedStatement ps = connection.prepareStatement(INSERT_SQL, Statement.RETURN_GENERATED_KEYS); 
					ps.setString(1, software.getNameSoftware()); 
					ps.setString(2, software.getVersionSoftware()); 
					ps.setString(3, software.getSoftwareVendor());
					ps.setInt(4, computerId);
					return ps; 
				} 
			}, keyHolder);
		} catch (DataAccessException e){
			LOG.debug("Failed to insert Software for ComputerId "+computerId);
			LOG.error(e.toString());
		}
		LOG.debug("Inserted Software with id: " + keyHolder.getKey());
		return keyHolder.getKey().intValue();
	}

	public List<Software> listSoftwares() {
		String SQL = "SELECT * FROM Software GROUP BY softName,softVersion,softVendor";
		List<Software> softwares = new ArrayList<Software>();
		try{
			softwares = jdbcTemplateObject.query(SQL,
				new SoftwareMapper());
		} catch (DataAccessException e){
			LOG.debug("Failed to load a list of Softwares");
			LOG.debug(e.toString());
		}
		LOG.debug("Loaded a list of Softwares");
		return softwares;
	}
	
	public Software readSoftwareById(Integer softwareId, String softwareName){
		String SQL = "SELECT * FROM Software WHERE softID_pk = ? AND softName = ?";
		Software software = new Software();
		try{
			software = jdbcTemplateObject.queryForObject(SQL, 
	                        new Object[]{softwareId, softwareName}, new SoftwareMapper());
		} catch (DataAccessException e){
			LOG.debug("Failed to read Software with id "+softwareId);
			LOG.error(e.toString());
		}
		LOG.debug("Read Software with Id = "+ software.getSoftwareId());  
	    return software;

	}
	
	public void deleteSoftwareById(Integer softwareId) {
		String SQL = "DELETE FROM Software WHERE softID_pk = ?";
		try{
			jdbcTemplateObject.update(SQL, softwareId);
		} catch(DataAccessException e){
			LOG.debug("Failed to delete Software with id "+softwareId);
			LOG.error(e.toString());
		}
		LOG.debug("Deleted Software with id = " + softwareId);
	}

	public List<Software> listAllSoftwaresFromComputer(Integer computerId) {
		String SQL = "SELECT * FROM Software WHERE soft_compID_fk = ?";
		List<Software> softwares = new ArrayList<Software>();
		try{
			softwares = jdbcTemplateObject.query(SQL, new Object[]{computerId},
				new SoftwareMapper());
		} catch (DataAccessException e){
			LOG.debug("Failed to load a list of Softwares for ComputerId "+computerId);
			LOG.error(e.toString());
		}
		LOG.debug("Loaded a list of Softwares for ComputerId "+computerId);
		return softwares;
	}
}
